<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;


class CabinetData extends Model
{
    protected $table = 'xfy_cabinet_data';
    protected $primaryKey = 'id';

    public static function getJsonDataOne($id, $fields, $time = 0){
        $sql = 'select ';

        foreach ($fields as $oneField) {
            if ($oneField == 'created_time') {
                $sql .= ' created_time,';
            } else {
                $sql .= "data->'$.".$oneField."' as ".$oneField.',';
            }
        }
        $sql = substr($sql,0,-1);
        $sql .= ' from xfy_cabinet_data ';

        $sql .= "where cabinet_id = ?";

        if ($time) {
            $sql .= " and created_time > ? ";
        }
        $sql .= " order by created_time desc ";
        $sql .= " limit 1";
        if ($time) {
            $res = DB::select($sql, [$id, $time]);
        } else {
            $res = DB::select($sql, [$id]);
        }

        $res = json_decode( json_encode( reset($res)),true);

        return $res;
    }

    public static function getMax($id, $field, $start, $end){
        $sql = "select max(data->'$.".$field."') as max from `xfy_cabinet_data` where `cabinet_id` = ? and `created_time` between ? and ?";
        $res = DB::select($sql, [$id, $start, $end]);
        $max = json_decode( json_encode( reset($res)),true);
        if ($max['max']) {
            return round($max['max'],3);
        } else {
            return 0;
        }
    }

    public static function getMin($id, $field, $start, $end){
        $sql = "select min(data->'$.".$field."') as min from `xfy_cabinet_data` where `cabinet_id` = ? and `created_time` between ? and ?";
        $res = DB::select($sql, [$id, $start, $end]);
        $min = json_decode( json_encode( reset($res)),true);
        if ($min['min']) {
            return round($min['min'],3);
        } else {
            return 0;
        }
    }

    public static function getAvg($id, $field, $start, $end){
        $sql = "select avg(data->'$.".$field."') as avg from `xfy_cabinet_data` where `cabinet_id` = ? and `created_time` between ? and ?";
        $res = DB::select($sql, [$id, $start, $end]);
        $avg = json_decode( json_encode( reset($res)),true);
        if ($avg['avg']) {
            return round($avg['avg'],3);
        } else {
            return 0;
        }
    }

    public static function getJsonDataList($id, $fields, $start, $end = 0){
        $sql = 'select ';

        if (!is_array($fields)) {
            $fields = [$fields];
        }

        foreach ($fields as $oneField) {
            $sql .= "data->'$.".$oneField."' as ".$oneField.',';
        }

        if ($end) {
            $sql .= " `created_time` from `xfy_cabinet_data` where `cabinet_id` = ? and `created_time` >= ? and `created_time` <= ? order by `created_time` asc";
            $res = DB::select($sql, [$id, $start, $end]);
        } else {
            $sql .= " `created_time` from `xfy_cabinet_data` where `cabinet_id` = ? and `created_time` > ? order by `created_time` asc";
            $res = DB::select($sql, [$id, $start]);
        }
        return $res;
    }

    public static function getJsonDataListPaging($id, $fields, $start, $end = 0, $page = 0, $size = 0){
        //LIMIT ((页码-1)*页大小),页大小;
        $sql = 'select ';

        if (!is_array($fields)) {
            $fields = [$fields];
        }

        foreach ($fields as $oneField) {
            $sql .= "data->'$.".$oneField."' as ".$oneField.',';
        }

        if ($end) {
            $sql .= " `created_time` from `xfy_cabinet_data` where `cabinet_id` = ? and `created_time` >= ? and `created_time` <= ? order by `created_time` desc";
            if ($page && $size) {
                $sql .= " limit ".(($page - 1) * $size).", ".$size;
            }
            $res = DB::select($sql, [$id, $start, $end]);
        } else {
            $sql .= " `created_time` from `xfy_cabinet_data` where `cabinet_id` = ? and `created_time` > ? order by `created_time` desc";
            if ($page && $size) {
                $sql .= " limit ".(($page - 1) * $size).", ".$size;
            }
            $res = DB::select($sql, [$id, $start]);
        }
        return $res;
    }

    public static function getOneFieldNewestData($id, $field){
        $sql = 'select ';
        $sql .= "data->'$.".$field."' as ".$field;
        $sql .= ' from xfy_cabinet_data ';
        $sql .= "where cabinet_id = ?";
        $sql .= " order by created_time desc ";
        $sql .= " limit 1";
        $res = DB::select($sql, [$id]);
        $res = json_decode( json_encode( reset($res)),true);
        return $res[$field];
    }

    public static function getCount($id, $start, $end){
        $sql = 'select count(*) as total';
        $sql .= ' from xfy_cabinet_data ';
        $sql .= "where `cabinet_id` = ? and `created_time` >= ? and `created_time` <= ?";
        $res = DB::select($sql, [$id, $start, $end]);
        $res = json_decode( json_encode( reset($res)),true);
        return $res['total'];
    }
}